﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using UserDefineForm.dao.orm;
using System.Data;
using UserDefineForm.exception;

namespace UserDefineForm.dao.supplierform
{
    public class SupplierFormDAO
    {
        private SqlConnection sqlConnection;

        private SupplierFormORM createFormORMFromReader(IDataReader reader)
        {
            SupplierFormORM item = new SupplierFormORM();
            try
            {
                if (!reader.IsDBNull(reader.GetOrdinal("ID"))) item.Id = (int)reader["ID"];
                if (!reader.IsDBNull(reader.GetOrdinal("Name"))) item.Name = (string)reader["Name"];
                if (!reader.IsDBNull(reader.GetOrdinal("Background"))) item.Background = (byte[])reader["Background"];
                if (!reader.IsDBNull(reader.GetOrdinal("OrderFormId"))) item.OrderFormId = (int)reader["OrderFormId"];
                if (!reader.IsDBNull(reader.GetOrdinal("Status"))) item.Status = (int)reader["Status"];
            }
            catch (Exception ex)
            {
                // wrap it and rethrow
                throw new DataReadException(ex.Message);
            }
            return item;
        }

        public SupplierFormDAO(SqlConnection sqlConnection)
        {
            this.sqlConnection = sqlConnection;
        }

        public SupplierFormORM getFormById(int id)
        {
            SqlDataReader reader = null;
            try
            {
                sqlConnection.Open();
                SqlCommand sqlCmd = new SqlCommand("GetFormById", sqlConnection);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.Parameters.Add(new SqlParameter("@id", id));

                reader = sqlCmd.ExecuteReader();
                if (reader.Read())
                {
                    SupplierFormORM result = createFormORMFromReader(reader);
                    return result;
                }
                else
                {
                    return null;
                }
            }
            finally
            {
                if (sqlConnection != null) sqlConnection.Close();
                if (reader != null) reader.Close();
            }
        }

        public void insertForm(SupplierFormORM formOrm)
        {
            SqlDataReader reader = null;
            try
            {
                sqlConnection.Open();
                SqlCommand sqlCmd = new SqlCommand("InsertForm", sqlConnection);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.Parameters.Add(new SqlParameter("@id", formOrm.Id));
                sqlCmd.Parameters["@id"].Direction = ParameterDirection.Output;

                sqlCmd.Parameters.Add(new SqlParameter("@name", formOrm.Name));
                sqlCmd.Parameters.Add(new SqlParameter("@background", formOrm.Background));
                sqlCmd.Parameters.Add(new SqlParameter("@orderFormId", formOrm.OrderFormId));
                sqlCmd.Parameters.Add(new SqlParameter("@status", formOrm.Status));

                sqlCmd.ExecuteNonQuery();

                formOrm.Id = (int)sqlCmd.Parameters["@id"].Value;
            }
            finally
            {
                if (sqlConnection != null) sqlConnection.Close();
                if (reader != null) reader.Close();
            }
        }

        public void updateForm(SupplierFormORM formOrm)
        {
            SqlDataReader reader = null;
            try
            {
                sqlConnection.Open();
                SqlCommand sqlCmd = new SqlCommand("UpdateForm", sqlConnection);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.Parameters.Add(new SqlParameter("@id", formOrm.Id));
                sqlCmd.Parameters.Add(new SqlParameter("@name", formOrm.Name));
                sqlCmd.Parameters.Add(new SqlParameter("@background", formOrm.Background));
                sqlCmd.Parameters.Add(new SqlParameter("@orderFormId", formOrm.OrderFormId));
                sqlCmd.Parameters.Add(new SqlParameter("@status", formOrm.Status));

                sqlCmd.ExecuteNonQuery();

            }
            finally
            {
                if (sqlConnection != null) sqlConnection.Close();
                if (reader != null) reader.Close();
            }
        }

        public List<SupplierFormORM> getAllForm()
        {
            SqlDataReader reader = null;
            try
            {
                sqlConnection.Open();
                SqlCommand sqlCmd = new SqlCommand("GetAllForm", sqlConnection);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                reader = sqlCmd.ExecuteReader();

                List<SupplierFormORM> result = new List<SupplierFormORM>();

                while (reader.Read())
                {
                    SupplierFormORM form = createFormORMFromReader(reader);
                    result.Add(form);
                }
                return result;
            }
            finally
            {
                if (sqlConnection != null) sqlConnection.Close();
                if (reader != null) reader.Close();
            }
        }

        public List<int> getAllFormIdAndName(out List<string> listName)
        {
            SqlDataReader reader = null;
            try
            {
                sqlConnection.Open();
                SqlCommand sqlCmd = new SqlCommand("GetAllForm", sqlConnection);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                reader = sqlCmd.ExecuteReader();

                List<int> listId = new List<int>();
                listName = new List<string>();

                while (reader.Read())
                {
                    int id=0;
                    string name="";

                    if (!reader.IsDBNull(reader.GetOrdinal("ID")))
                    {
                        id = (int)reader["ID"];
                    }
                    else
                    {
                        return null;
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("Name")))
                    {
                        name = (string)reader["Name"];
                    }
                    else
                    {
                        return null;
                    }

                    listId.Add(id);
                    listName.Add(name);

                }
                return listId;
            }
            finally
            {
                if (sqlConnection != null) sqlConnection.Close();
                if (reader != null) reader.Close();
            }
        }

        public void deleteForm(int id)
        {
            SqlDataReader reader = null;
            try
            {
                sqlConnection.Open();
                SqlCommand sqlCmd = new SqlCommand("DeleteForm", sqlConnection);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.Parameters.Add(new SqlParameter("@id", id));

                sqlCmd.ExecuteNonQuery();

            }
            finally
            {
                if (sqlConnection != null) sqlConnection.Close();
                if (reader != null) reader.Close();
            }
        }



    }
}
